DROP DATABASE IF EXISTS nabu;
CREATE DATABASE IF NOT EXISTS nabu 
	CHARACTER SET ='utf8mb4'
	COLLATE = 'utf8mb4_spanish_ci';
USE nabu;

SQL: CREATE TABLE `roles` (
  `id` TINYINT UNSIGNED NOT NULL,
  name VARCHAR(20) NOT NULL,
  CONSTRAINT roles_id_pk PRIMARY KEY(id),
  CONSTRAINT roles_name_uk UNIQUE(name)
) CHARSET=UTF8;
SQL: CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` TINYINT UNSIGNED NOT NULL DEFAULT 3,
  `name` VARCHAR(255) NOT NULL,
  `username` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `activated` TINYINT(1) NOT NULL DEFAULT FALSE,
  creation_date DATETIME,
  CONSTRAINT users_id_pk PRIMARY KEY(id),
  CONSTRAINT users_username_uk UNIQUE(username),
  CONSTRAINT users_email_uk UNIQUE(email),
  CONSTRAINT users_role_id_fk FOREIGN KEY(role_id) REFERENCES roles(id) ON UPDATE RESTRICT ON DELETE RESTRICT
) CHARSET=UTF8;
SQL: CREATE TABLE `verifications` (
  `id` INT UNSIGNED NOT NULL,
  `hash` VARCHAR(255),
  `expiration` INT UNSIGNED NOT NULL DEFAULT 0,
  CONSTRAINT verifications_id_pk PRIMARY KEY(id),
  CONSTRAINT verifications_id_fk FOREIGN KEY(id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARSET=UTF8;
SQL: CREATE TABLE `profiles` (
  `id` INT UNSIGNED NOT NULL,
  `avatar_file` VARCHAR(255),
  `background_file` VARCHAR(255),
  `description` VARCHAR(255),
  CONSTRAINT profiles_id_pk PRIMARY KEY(id),
  CONSTRAINT profiles_id_fk FOREIGN KEY(id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT profiles_avatar_file_uk UNIQUE(avatar_file),
  CONSTRAINT profiles_background_file_uk UNIQUE(background_file)
) CHARSET=UTF8;
SQL: CREATE TABLE `articles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `synopsis` VARCHAR(255),
  content MEDIUMTEXT NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `cover_file` VARCHAR(255),
  `authorized` TINYINT(1) NOT NULL DEFAULT FALSE,
  creation_date DATETIME,
  modification_date DATETIME,
  CONSTRAINT articles_id_pk PRIMARY KEY(id),
  CONSTRAINT articles_user_id_fk FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT articles_url_uk UNIQUE(slug),
  CONSTRAINT articles_cover_file_uk UNIQUE(cover_file)
) CHARSET=UTF8;
SQL: CREATE TABLE `categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  CONSTRAINT categories_id_pk PRIMARY KEY(id),
  CONSTRAINT categories_name_uk UNIQUE(name)
) CHARSET=UTF8;
SQL: CREATE TABLE `categories_article` (
  `article_id` INT UNSIGNED NOT NULL,
  `categorie_id` INT UNSIGNED NOT NULL,
  CONSTRAINT categories_article_pk PRIMARY KEY(article_id,categorie_id),
  CONSTRAINT categories_article_article_id_fk FOREIGN KEY(article_id) REFERENCES articles(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT categories_categorie_id_fk FOREIGN KEY(categorie_id) REFERENCES categories(id) ON UPDATE RESTRICT ON DELETE RESTRICT
) CHARSET=UTF8;
SQL: CREATE TABLE `authorizations` (
  `article_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  authorization_date DATETIME NOT NULL,
  CONSTRAINT authorizations_pk PRIMARY KEY(article_id,authorization_date),
  CONSTRAINT authorizations_article_id_fk FOREIGN KEY(article_id) REFERENCES articles(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT authorizations_user_id_fk FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT
) CHARSET=UTF8;
SQL: CREATE TABLE `comments` (
  `user_id` INT UNSIGNED NOT NULL,
  `article_id` INT UNSIGNED NOT NULL,
  `body` VARCHAR(255) NOT NULL,
  comment_date DATETIME NOT NULL,
  CONSTRAINT comments_pk PRIMARY KEY(user_id,comment_date),
  CONSTRAINT comments_user_id_fk FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT comments_article_id_fk FOREIGN KEY(article_id) REFERENCES articles(id) ON UPDATE RESTRICT ON DELETE RESTRICT
) CHARSET=UTF8;
SQL: CREATE TABLE `favorites` (
  `user_id` INT UNSIGNED NOT NULL,
  `article_id` INT UNSIGNED NOT NULL,
  CONSTRAINT favorites_pk PRIMARY KEY(user_id,article_id),
  CONSTRAINT favorites_user_id_fk FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT favorites_article_id_fk FOREIGN KEY(article_id) REFERENCES articles(id) ON UPDATE RESTRICT ON DELETE RESTRICT
) CHARSET=UTF8;
